<?php
/**
 * dbfront
 * (c) 2010-2013 Stephen Adkins <spadkins@gmail.com>
 * This software began development in 2010.
 * It is based on code dating from 1999.
 *
 * License: GPL 2.0
 * The contents of this file may be used under the terms of
 * the GNU General Public License Version 2 or later (the "GPL").
 *
 * Software distributed under the License is distributed on an "AS IS" basis,
 * WITHOUT WARRANTY OF ANY KIND, either express or implied. See the License
 * for the specific language governing rights and limitations under the
 * License.
 * */
namespace API;
class Ext extends \App\SessionObject {
    /**
     * apptree(...) - serves as the URL endpoint for an arbitrary, named, application-defined ExtJS tree whose nodes are stored in the 'app_tree' table
     * In this routine, all tree node ID's are represented by integers which are the primary key values of the nodes as they are stored in the 'app_tree' table.
     * (op = 'new')    requires 'parent_id' and 'label'.        response: {'success': true, 'tree_id': newTreeNodeId}
     * (op = 'delete') requires 'tree_node_id'.                 response: {'success': true}
     * (op = 'move')   requires 'parent_id' and 'tree_node_id'. response: {'success': true} - moves tree_node_id to the parent, subject to any supplied previous_sibling_id, next_sibling_id, and drop_position
     * (op = 'rename') requires 'tree_node_id' and 'label'.     response: {'success': true} - changes the label on the node identified by tree_node_id.
     * (op = 'get')
     * (1) if 'node' and 'id' are both 'src', the list of databases are returned as a JSON array of 
     * (1)               {'id'=>{tree_id}, 'text'=>{label}, 'expanded'=>false, 'leaf'=>{leafInd}, 'action'=>{action}, 'node_type'=>'database'}
     * (2) if 'node' and 'id' are are not both 'src', then 'node' is assumed to be an integer tree_node_id
     * (2) The response is then a JSON array of children, describing the children of the 'node' queried.
     * (2)               {'id'=>{tree_id}, 'text'=>{label}, 'expanded'=>false, 'leaf'=>{leafInd}, 'action'=>{action}, 'node_type'=>'table'}
     * (The possible types are 'database', 'table', 'view', 'graph', 'dashboard', 'folder', 'leaf', 'unknown'.)
     * (The design of this API is based solely on the current understanding of what ExtJS trees naturally expect.)
     * (It is possible that the API could be cleaned up or made more consistent by using certain undetermined configuration settings on the ExtJS tree.)
     * @param string  type                [IN] - the name of the user-defined tree (e.g. 'organizer', 'views')
     * @param string  op                  [IN] - (defaults to 'get') the operation ('get', 'new', 'delete', 'rename', or 'move')
     * @param string  parent_id           [IN] - [used for 'new', 'move']
     * @param integer tree_node_id        [IN] - [used for 'delete', 'rename', 'move']
     * @param string  label               [IN] - [used for 'new', 'rename'] the label that will be displayed for the node.
     * @param string  node_type           [IN] - [used for 'new'] a node type (e.g. 'database', 'table', 'view', 'graph', 'dashboard', 'folder', 'leaf', 'unknown')
     * @param string  action              [IN] - [used for 'new'] any node with an action is a leaf. the action string indicates what action should be performed if the user clicks on the node.
     * @param string  previous_sibling_id [IN] - [used for 'move']
     * @param string  next_sibling_id     [IN] - [used for 'move']
     * @param string  drop_position       [IN] - [used for 'move'] ('before', 'after', 'append', 'prepend')
     * @param string  mode                [IN] - [used for 'get'] the mode of operation (defaults to 'getTree' and 'getTree' is currently the only defined mode)
     * @param string  node                [IN] - [used for 'get'] (required) the node in the tree being queried ('src' is the root of the tree)
     * @param string  id                  [IN] - [used for 'get'] (optional) ignored for now
     * @param integer full                [IN] - [used for 'get'] (optional) (integer-as-boolean) (defaults to 0) return the entire tree instead of a piece at a time (not yet fully functional)
     */
    public function apptree ($request, &$response) {
        global $context, $options, $debug_sql, $trace;
        if ($trace) trace_entry();
        $response = array();
        $metadb   = $context->database('meta');
        $neat     = (isset($request['neat']) && $request['neat']) ? 1 : 0;
        $type     = isset($request['type']) ? $request['type'] : null;
        if (!isset($type)) throw new \Exception('ERROR: type parameter must be set');
        $owner_group_id = $context->getAuthenticatedGroupId();

        $op      = (isset($request['op']) && $request['op']) ? $request['op'] : 'get';

        if ($op != 'get') {
            $tree_node_id        = (isset($request['tree_node_id'])        && $request['tree_node_id'])        ? $request['tree_node_id']        : null;
            if (isset($tree_node_id) && !preg_match('/^[0-9]+$/',$tree_node_id)) throw new \Exception("ERROR: tree_node_id is not numeric");

            $parent_id           = (isset($request['parent_id'])           && $request['parent_id'])           ? $request['parent_id']           : null;
            if ($parent_id === 'src') $parent_id = null;
            elseif ($parent_id && !preg_match('/^[0-9]+$/',$parent_id)) throw new \Exception("ERROR: parent_id is not numeric");

            $label               = (isset($request['label'])     && $request['label'])     ? $request['label']     : null;
            $action              = (isset($request['action'])    && $request['action'])    ? $request['action']    : null;
            $node_type           = (isset($request['node_type']) && $request['node_type']) ? $request['node_type'] : $this->_get_default_node_type(null, $action);

            $previous_sibling_id = (isset($request['previous_sibling_id']) && $request['previous_sibling_id']) ? $request['previous_sibling_id'] : null;
            if (isset($previous_sibling_id) && !preg_match('/^[0-9]+$/',$previous_sibling_id)) throw new \Exception("ERROR: previous_sibling_id is not numeric");

            $next_sibling_id     = (isset($request['next_sibling_id'])     && $request['next_sibling_id'])     ? $request['next_sibling_id']     : null;
            if (isset($next_sibling_id) && !preg_match('/^[0-9]+$/',$next_sibling_id)) throw new \Exception("ERROR: next_sibling_id is not numeric");

            # drop_position values: [before, after, append, prepend]
            $drop_position       = (isset($request['drop_position'])       && $request['drop_position'])       ? $request['drop_position']       : null;

            $response['success']  = true;   # assume we will be successful
        }

        if ($op === 'new') {
            if (!isset($label))     throw new \Exception("ERROR: label must be supplied for a 'new' operation");
            $leaf_ind = isset($action) ? 'Y' : 'N';
            $metadb->execute("insert into {meta_schema_}app_tree (tree_type, tree_label, node_type, action, leaf_ind, parent_tree_id, order_idx, owner_group_id) values (?, ?, ?, ?, ?, ?, ?, ?)",
                array($type, $label, $node_type, $action, $leaf_ind, $parent_id, 9999, $owner_group_id));
            $response['tree_id'] = $metadb->last_insert_id("app_tree");
            $this->_reorder_tree($metadb, $type, $parent_id);
        }
        elseif ($op === 'delete') {
            if (!isset($tree_node_id)) throw new \Exception("ERROR: tree_node_id must be supplied for a 'delete' operation");
            $metadb->execute("delete from {meta_schema_}app_tree where tree_id = ? and owner_group_id = ?", array($tree_node_id, $owner_group_id));
        }
        elseif ($op === 'rename') {
            if (!isset($tree_node_id))     throw new \Exception("ERROR: tree_node_id must be supplied for a 'rename' operation");
            if (!isset($label) || !$label) throw new \Exception("ERROR: label must be supplied for a 'rename' operation");
            $metadb->execute("update {meta_schema_}app_tree set tree_label = ? where tree_id = ? and owner_group_id = ?", array($label, $tree_node_id, $owner_group_id));
        }
        elseif ($op === 'move') {
            if (!isset($tree_node_id)) throw new \Exception("ERROR: tree_node_id must be supplied for a 'move' operation");
            $this->_reorder_tree($metadb, $type, $parent_id, $tree_node_id, $previous_sibling_id, $next_sibling_id, $drop_position);
        }
        else {
            $matches = array();
            $get_root_node = 0;
            $mode = (isset($request['mode'])) ? $request['mode'] : 'getTree';
            if ($mode === 'getTree') {
                if (isset($request['node'])) {
                    $node = $request['node'];
                    #echo "node=[$node] mode=[$mode] id=[$id]\n";
                    if ($node === 'src') {
                        $get_root_node = 1;
                    }
                    else {
                        $sql = <<<EOF
select
    t.tree_id,
    t.tree_label,
    t.leaf_ind,
    t.node_type,
    t.action,
    count(t2.tree_id) as num_children
from {meta_schema_}app_tree t
     left join {meta_schema_}app_tree t2 on t2.parent_tree_id = t.tree_id and t2.owner_group_id = t.owner_group_id
where t.tree_type = ?
  and t.parent_tree_id = ?
  and t.owner_group_id = ?
group by
    t.tree_id,
    t.tree_label,
    t.leaf_ind,
    t.node_type,
    t.action
order by
    t.order_idx,
    t.tree_label
EOF;
                        $tree_nodes = $metadb->get_hashes($sql, array($type, $node, $owner_group_id));
                        foreach ($tree_nodes as $tree_node) {
                            $is_leaf = (isset($tree_node['action'])) ? true : false;
                            $expanded = ($is_leaf || (isset($tree_node['num_children']) && $tree_node['num_children'] == 0)) ? true : false;
                            #$leaf_ind  = $tree_node['leaf_ind'];
                            $action    = $tree_node['action'];
                            $node_type = $tree_node['node_type'];
                            if (!isset($node_type)) { $node_type = $this->_get_default_node_type($is_leaf, $action); }
                            $response[] = array('id'=>$tree_node['tree_id'], 'text'=>$tree_node['tree_label'], 'expanded'=>$expanded,
                                                'leaf'=>$is_leaf, 'node_type'=>$node_type, 'action'=>$action);
                        }
                    }
                }
                else {
                    throw new \Exception("ERROR: node and id must both be set (often, these are the same) ('src' is the root node)");
                }
            }
            else {
                throw new \Exception("ERROR: unrecognized mode on tree [$mode] (did you mean 'getTree'?)");
            }
            if ($get_root_node) {
                $full = (isset($request['full']) && $request['full']) ? 1 : 0;
                $sql = <<<EOF
select
    t.tree_id,
    t.tree_label,
    t.leaf_ind,
    t.node_type,
    t.action,
    count(t2.tree_id) as num_children
from {meta_schema_}app_tree t
     left join {meta_schema_}app_tree t2 on t2.parent_tree_id = t.tree_id and t2.owner_group_id = t.owner_group_id
where t.tree_type = ?
  and t.parent_tree_id is null
  and t.owner_group_id = ?
group by
    t.tree_id,
    t.tree_label,
    t.leaf_ind,
    t.node_type,
    t.action
order by
    t.order_idx,
    t.tree_label
EOF;
                $tree_nodes = $metadb->get_hashes($sql, array($type, $owner_group_id));
                $response = array();
                if ($full) {
                    foreach ($tree_nodes as $tree_node) {
                        $is_leaf = (isset($tree_node['action'])) ? true : false;
                        $expanded = ($is_leaf || (isset($tree_node['num_children']) && $tree_node['num_children'] == 0)) ? true : false;
                        #$leaf_ind  = $tree_node['leaf_ind'];
                        $action    = isset($tree_node['action'])    ? $tree_node['action']    : null;
                        $node_type = isset($tree_node['node_type']) ? $tree_node['node_type'] : null;
                        if (!isset($node_type)) { $node_type = $this->_get_default_node_type($is_leaf, $action); }
                        $response[] = array('id'=>$tree_node['tree_id'], 'text'=>$tree_node['tree_label'], 'expanded'=>$expanded,
                                            'leaf'=>$is_leaf, 'node_type'=>$node_type, 'action'=>$action);
                    }
                    # do more stuff here ...
                }
                else {
                    foreach ($tree_nodes as $tree_node) {
                        $is_leaf = (isset($tree_node['action'])) ? true : false;
                        $expanded = ($is_leaf || (isset($tree_node['num_children']) && $tree_node['num_children'] == 0)) ? true : false;
                        #$leaf_ind  = $tree_node['leaf_ind'];
                        $action    = isset($tree_node['action'])    ? $tree_node['action']    : null;
                        $node_type = isset($tree_node['node_type']) ? $tree_node['node_type'] : null;
                        if (!isset($node_type)) { $node_type = $this->_get_default_node_type($is_leaf, $action); }
                        $response[] = array('id'=>$tree_node['tree_id'], 'text'=>$tree_node['tree_label'], 'expanded'=>$expanded,
                                            'leaf'=>$is_leaf, 'node_type'=>$node_type, 'action'=>$action);
                    }
                }
            }
        }
        if ($trace) trace_exit($response);
    }

    private function _get_default_node_type ($is_leaf, $action) {
        if (isset($is_leaf) && !$is_leaf) {
            $node_type = 'folder';
        }
        elseif (!isset($action)) {
            $node_type = 'folder';
        }
        else {
            $action_parts = explode('.', $action);
            $count_action_parts = count($action_parts);
            if     ($count_action_parts == 1) { $node_type = 'database'; }
            elseif ($count_action_parts == 2) { $node_type = 'table'; }
            elseif ($count_action_parts == 3) { $node_type = 'view'; }
            else                              { $node_type = 'other'; }
        }
        return($node_type);
    }

    private function _reorder_tree ($metadb, $type, $parent_id, $tree_node_id = 0, $previous_sibling_id = 0, $next_sibling_id = 0, $drop_position = '') {
        global $trace;
        if ($trace) trace_entry();
        $quoted_type     = $metadb->_quote($type);
        if (!isset($parent_id)) {
            $parent_id_where_clause = 'is NULL';
            $parent_id = 'NULL';
        }
        else {
            $parent_id_where_clause = "= $parent_id";
        }
        $siblings = $metadb->get_hashes("select tree_id, order_idx from {meta_schema_}app_tree where tree_type = $quoted_type and parent_tree_id $parent_id_where_clause order by order_idx, tree_label");
        $order_idx = 1;
        $node_done = $tree_node_id ? 0 : 1;
        $reorder_reqd = ($previous_sibling_id || $next_sibling_id || $drop_position) ? 1 : 0;
        if (!$node_done && $drop_position === 'prepend') {
            $nrows = $metadb->execute("update {meta_schema_}app_tree set parent_tree_id = $parent_id, order_idx = $order_idx where tree_id = $tree_node_id");
            $order_idx++;
            $node_done = 1;
        }
        foreach ($siblings as $sibling) {
            if (!$node_done && $next_sibling_id && $sibling['tree_id'] == $next_sibling_id) {
                $nrows = $metadb->execute("update {meta_schema_}app_tree set parent_tree_id = $parent_id, order_idx = $order_idx where tree_id = $tree_node_id");
                $order_idx++;
                $node_done = 1;
            }
            if ($tree_node_id && $sibling['tree_id'] == $tree_node_id) {
                if (!$node_done && !$reorder_reqd) {
                    $nrows = $metadb->execute("update {meta_schema_}app_tree set parent_tree_id = $parent_id, order_idx = $order_idx where tree_id = $tree_node_id");
                    $order_idx++;
                    $node_done = 1;
                }
            }
            else {
                if ($sibling['order_idx'] != $order_idx) {
                    $nrows = $metadb->execute("update {meta_schema_}app_tree set order_idx = $order_idx where tree_id = $sibling[tree_id]");
                }
                $order_idx++;
            }
            if (!$node_done && $previous_sibling_id && $sibling['tree_id'] == $previous_sibling_id) {
                $nrows = $metadb->execute("update {meta_schema_}app_tree set parent_tree_id = $parent_id, order_idx = $order_idx where tree_id = $tree_node_id");
                $order_idx++;
                $node_done = 1;
            }
        }
        if (!$node_done) {
            $nrows = $metadb->execute("update {meta_schema_}app_tree set parent_tree_id = $parent_id, order_idx = $order_idx where tree_id = $tree_node_id");
            $order_idx++;
            $node_done = 1;
        }
        if ($trace) trace_exit();
    }

    /**
     * tabletree(mode, node) - serves as the URL endpoint for a specific kind of ExtJS tree, serving up a hierarchy of database/schema/table/view.
     * (1) if 'node' is 'src', the list of databases are returned as a JSON array of {'id'=>$database, 'text'=>$database, 'expanded'=>false} structures
     * (2) if 'node' is not 'src', the 'node' is assumed to be of the form '{db_name}', '{db_name}.{table}' or '{db_name}.{table}.{view}'
     * If the user is authorized, the 'node' may also be of the form '_phys', '_phys.{dbcid}', '_phys.{dbcid}.{schema}', '_phys.{dbcid}.{schema}.{table}' or '_phys.{dbcid}.{schema}.{table}.{view}'
     * The response is then a JSON object with 'success' and 'children' set, describing the children of the 'node' queried
     * Children of the root (databases) are: {'id'=>'{db_name}', 'text'=>'{db_name}', 'expanded'=>false}
     * Children of a database (tables) are: {'id'=>'{db_name}.{table}', 'text'=>'{table}', 'expanded'=>false}
     * Children of a table (views) are: {'id'=>'{db_name}.{table}.{view}', 'text'=>'{view}', 'expanded'=>false}
     * note: all {db_name}, {table}, and {view} segments must match the regular expression: [a-zA-Z_][a-zA-Z0-9_]*
     * (The design of this API is based solely on the current understanding of what ExtJS trees naturally expect.)
     * (It is possible that the API could be cleaned up or made more consistent by using certain undetermined configuration settings on the ExtJS tree.)
     * @param string mode [IN] - the mode of operation (defaults to 'getTree' and 'getTree' is currently the only defined mode)
     * @param string node [IN] - (required) the node in the tree being queried ('src' is the root of the tree)
     * @param string id   [IN] - (optional) currently ignored
     * @param integer full [IN] - (optional) (integer-as-boolean) (defaults to 0) return the entire tree instead of a piece at a time
     * @param boolean success [OUT] - (when querying a node below the root)
     * @param array children [OUT] - (when querying a node below the root) an array of child objects
     */
    public function tabletree ($request, &$response) {
        global $context, $options, $debug_sql, $trace;
        if ($trace) trace_entry();
        $group_id  = $context->getAuthenticatedGroupId();
        $neat      = (isset($request['neat']) && $request['neat']) ? 1 : 0;
        $tree      = array();
        $matches   = array();
        $get_root_node = 0;
        $mode      = isset($request['mode']) ? $request['mode'] : 'getTree';

        if ($mode === 'getTree') {
            if (isset($request['node'])) {
                $node = $request['node'];
                #$id   = isset($request['id']) ? $request['id'] : '';
                #echo "node=[$node] mode=[$mode] id=[$id]\n";
                if ($node === 'src') {
                    $get_root_node = 1;
                }
                elseif (preg_match('/^_phys\\./', $node)) {
                    if (preg_match('/^_phys\\.([a-zA-Z0-9_]+)$/', $node, $matches)) {
                        $database = $matches[1];
                        $db = $context->database($database);
                        $schemas = $db->get_schema_names();
                        $database_children = array();
                        foreach ($schemas as $schema) {
                            $database_children[] = array('id'=>"$database.$schema", 'text'=>$schema, 'expanded'=>false);
                        }
                        $response['success'] = true;
                        $response['children'] = $database_children;
                    }
                    elseif (preg_match('/^_phys\\.([a-zA-Z0-9_]+)\\.([a-zA-Z0-9_]+)$/', $node, $matches)) {
                        $database = $matches[1];
                        $schema   = $matches[2];
                        $rows     = $metadb->get_rows('select t.table_name, t.table_label from db inner join db_schema s on s.db_connection_id = db.db_connection_id inner join db_table t on t.schema_id = s.schema_id where db.owner_group_id = ? and db.db_name = ? and s.owner_group_id = ? and s.schema_name = ?', array($group_id, $database, $group_id, $schema));
                        $schema_children = array();
                        foreach ($rows as $row) {
                            #echo "table=[$table]\n";
                            $schema_children[] = array('id'=>"$database.$schema.$row[0]", 'text'=>$row[1], 'expanded'=>false, 'view'=>true);
                        }
                        $response['success'] = true;
                        $response['children'] = $schema_children;
                    }
                    elseif (preg_match('/^_phys\\.([a-zA-Z0-9_]+)\\.([a-zA-Z0-9_]+)\\.([a-zA-Z0-9_]+)$/', $node, $matches)) {
                        $database = $matches[1];
                        $schema   = $matches[2];
                        $table    = $matches[3];
                        $rows = $metadb->get_rows("select v.view_key, v.view_label from db_view v where v.owner_group_id = ? and v.view_key like ?", array($group_id, "$database.$table.%"));
                        $schema_children = array();
                        foreach ($rows as $row) {
                            list($fullview, $label) = $row;
                            if (!$label) {
                                if (preg_match("/^$database\\.$table\\.(.+)$/", $fullview, $matches)) {
                                    $label = $matches[1];
                                }
                                else {
                                    $label = $fullview;
                                }
                            }
                            #echo "table=[$table]\n";
                            $data_url = $options['version_urldir'].'/'.$options['version']."/api/object/$database/$table";
                            $schema_children[] = array('id'=>$fullview, 'text'=>$label, 'node_type'=>'table', 'data_url'=>$data_url, 'leaf'=>true, 'view'=>true);
                        }
                        $response['success'] = true;
                        $response['children'] = $schema_children;
                    }
                    else {
                        throw new \Exception("ERROR: unrecognized node in tree [$node]");
                    }
                }
                else {
                    // database selected. get a list of tables.
                    if (preg_match('/^([a-zA-Z0-9_]+)$/', $node, $matches)) {
                        $database = $matches[1];
                        $db = $context->database($database);
                        $schema_name = $db->get_schema_name();
                        $table_defs = $db->get_table_defs_2($schema_name);
                        $database_children = array();
                        foreach ($table_defs as $table_name => $table_def) {
                            #echo "table=[$table]\n";
                            $data_url = $options['version_urldir'].'/'.$options['version']."/api/object/$database/$table_name";
                            $is_leaf = (isset($table_def['num_children']) && $table_def['num_children'] == 0) ? true : false;
                            $database_children[] = array('id'=>"$database.$table_name", 'text'=>$table_def['table_label'], 'node_type'=>'table', 'data_url'=>$data_url, 'expanded'=>false, 'view'=>true, 'leaf'=>$is_leaf);
                        }
                        $response['success'] = true;
                        $response['children'] = $database_children;
                    }
                    // database and table selected. get a list of views.
                    elseif (preg_match('/^([a-zA-Z0-9_]+)\\.([a-zA-Z0-9_]+)$/', $node, $matches)) {
                        $database = $matches[1];
                        $table    = $matches[2];
                        $db = $context->database($database);
                        $view_defs = $db->get_view_defs($table);
                        $schema_children = array();
                        foreach ($view_defs as $view_def) {
                            $view_name  = $view_def['view_name'];
                            $view_label = $view_def['view_label'];
                            $is_leaf = (isset($view_def['num_children']) && $view_def['num_children'] == 0) ? true : false;
                            if (!$view_label) {
                                if (preg_match("/^$database\\.$table\\.(.+)$/", $view_name, $matches)) {
                                    $view_label = $matches[1];
                                }
                                else {
                                    $view_label = $view_name;
                                }
                            }
                            #echo "table=[$table]\n";
                            $data_url = $options['version_urldir'].'/'.$options['version']."/api/object/$database/$table?view=$view_name";
                            $schema_children[] = array('id'=>$view_name, 'text'=>$view_label, 'node_type'=>'view', 'data_url'=>$data_url, 'view'=>true,'leaf'=>$is_leaf);
                        }
                        $response['success'] = true;
                        $response['children'] = $schema_children;
                    }
                    elseif (preg_match('/^([a-zA-Z0-9_]+)\\.([a-zA-Z0-9_]+)\\.([a-zA-Z0-9_]+)$/', $node, $matches)) {
                        $database  = $matches[1];
                        $table     = $matches[2];
                        $view_code = $matches[3];
                        $view      = $node;
                        $db = $context->database($database);
                        $chart_defs = $db->get_chart_defs($view);
                        $view_children = array();
                        foreach ($chart_defs as $chart_def) {
                            $chart_name  = $chart_def['chart_name'];
                            $chart_label = $chart_def['chart_label'];
                            #echo "table=[$table]\n";
                            $data_url = $options['version_urldir'].'/'.$options['version']."/api/object/$database/$table?view=$view";
                            $view_children[] = array('id'=>$chart_name, 'text'=>$chart_label, 'node_type'=>'chart', 'data_url'=>$data_url, 'leaf'=>true);
                        }
                        $response['success'] = true;
                        $response['children'] = $view_children;
                    }
                    else {
                        throw new \Exception("ERROR: that node [$node] can have no children");
                    }
                }
            }
            else {
                throw new \Exception("ERROR: node must be set");
            }
        }
        else {
            throw new \Exception("ERROR: unrecognized mode on tree [$mode]");
        }

        if ($get_root_node) {
            $full = (isset($request['full']) && $request['full']) ? 1 : 0;
            $authdb    = $context->authdb();
            $database_defs = $context->get_database_defs();
            foreach ($database_defs as $database_def) {
                $db_name = $database_def['db_name'];
                if ($full) {
                    $database_children = array();
                    $db = $context->database($db_name);
                    $schemas = $db->get_schema_names();
                    foreach ($schemas as $schema) {
                        #echo "schema=[$schema]\n";
                        $schema_children = array();
                        $tables = $db->get_table_names($schema);
                        foreach ($tables as $table) {
                            #echo "table=[$table]\n";
                            $table_def = $db->get_table_def($table, $schema);
                            $data_url = $options['version_urldir'].'/'.$options['version']."/api/object/$database/$table";
                            $schema_children[] = array('id'=>"$db_name.$schema.$table", 'text'=>$table_def['label'], 'node_type'=>'table', 'data_url'=>$data_url, 'leaf'=>true);
                        }
                        $database_children[] = array('id'=>"$db_name.$schema", 'text'=>$schema, 'node_type'=>'phys_schema', 'expanded'=>false, 'children'=>$schema_children);
                    }
                    $response[] = array('id'=>$db_name, 'text'=>$db_name, 'node_type'=>'database', 'expanded'=>false, 'children'=>$database_children);
                }
                else {
                    $response[] = array('id'=>$db_name, 'text'=>$db_name, 'node_type'=>'database', 'expanded'=>false);
                }
            }
        }
        if ($trace) trace_exit($response);
    }

    /**
     * filetree(...) - serves as the URL endpoint for an ExtJS tree whose nodes represent the file/directory system of the current group
     * In this routine, all tree node ID's are represented by file/directory paths from the root of the group's directory tree.
     * (op = 'mkdir')   requires 'dir_path'. optionally 'dir_name'. response: {'success': true} - creates the new 'dir_name' directory in the existing 'dir_path' directory
     * (op = 'delete')  requires 'dir_path'.                        response: {'success': true} - removes a file or directory (recursively if 'force' option is given)
     * (op = 'move')    requires 'from', 'to'.                      response: {'success': true} - moves source file or directory to the target file or directory
     * (op = 'getTree')
     * (1) if 'node' is 'src', the current directory is the user's home directory, and the list of all files and directories in it is returned.
     * (1)               {'id'=>{id}, 'text'=>{filename}, 'expanded'=>false, 'leaf'=>{leafInd}}
     * (2) if 'node' is not 'src', then 'node' is the relative subdirectory from the user's home directory, and the list of all files and directories in it is returned.
     * (2) The response is then a JSON array of children, describing the children of the 'node' queried.
     * (2)               {'id'=>{id}, 'text'=>{filename}, 'expanded'=>false, 'leaf'=>{leafInd}}
     * (The design of this API is based solely on the current understanding of what ExtJS trees naturally expect.)
     * (It is possible that the API could be cleaned up or made more consistent by using certain undetermined configuration settings on the ExtJS tree.)
     * @param string  op        [IN] - (defaults to 'getTree') the operation ('getTree', 'move', 'delete', or 'mkdir')
     * @param string  node_type [IN] - [used for 'getTree'] - only directory contents which match 'directory' or 'file' types are returned
     * @param string  glob      [IN] - [used for 'getTree'] - only directory contents which match the glob pattern (e.g. *.csv) are returned
     * @param string  from      [IN] - [used for 'move'] - relative source file name or directory (relative to the group's root directory) (without '..')
     * @param string  to        [IN] - [used for 'move'] - relative source file name or directory (relative to the group's root directory) (without '..')
     * @param string  dir_path  [IN] - [used for 'mkdir', 'delete'] - a relative path to a directory (or file) (relative to the group's root directory) (without '..')
     * @param string  dir_name  [IN] - [used for 'mkdir'] (optional) - a new directory to create in the dir_path (without any '/')
     * @param string  force     [IN] - [used for 'delete'] if the 'dir_path' is a non-empty directory, 'force' will force it to be deleted
     * @param boolean success   [OUT] - indicates whether the call was successful
     * @param string  message   [OUT] - if (success == false), the message will tell why it failed
     * @param string  id        [OUT] - 'src' at the root directory
     * @param string  text      [OUT] - 'files' is the label of the root node
     * @param array   children  [OUT] - nested array of child objects
     */
    public function filetree ($request, &$response) {
        global $context, $options, $debug_sql, $trace;
        if ($trace) trace_entry();

        $test_mode = isset($options['test_mode']) ? $options['test_mode'] : 0;
        $op        = isset($request['op'])        ? $request['op']        : 'getTree';
        $neat      = (isset($request['neat']) && $request['neat']) ? 1    : 0;
        $user_document_root = $context->getOwnerDir();
        
        if ($op === 'getTree') {
            $node = isset($request['node']) ? $request['node'] : 'src';
            $glob = isset($request['glob']) ? $request['glob'] : '*';
            $reqd_node_type = isset($request['node_type']) ? $request['node_type'] : null;
            if ($node === 'src') {
                $dir = $user_document_root;
                $item_prefix = '';
            }
            elseif (preg_match('!^[a-zA-Z0-9/\\._-]+$!', $node) && !preg_match('/\\.\\./', $node)) {
                $dir = "$user_document_root/$node";
                $item_prefix = "$node/";
            }
            else {
                throw new \Exception("ERROR: illegal node in tree [$node]");
            }
            foreach (glob("$dir/$glob") as $filepath) {
                $filename = basename($filepath);
                $id       = "{$item_prefix}{$filename}";
                if (is_dir($filepath)) {
                    if ($reqd_node_type === null || $reqd_node_type === 'directory') {
                        $response[] = array('id'=>$id, 'text'=>$filename, 'node_type'=>'directory', 'expanded'=>false);
                    }
                }
                elseif (is_file($filepath)) {
                    if ($reqd_node_type === null || $reqd_node_type === 'file') {
                        $response[] = array('id'=>$id, 'text'=>$filename, 'node_type'=>'file', 'leaf'=>true);
                    }
                }
            }
        }
        else if ($op === 'move') {
            $response['success'] = true;
            if (! isset($request['from']) || strlen($request['from']) === 0) throw new \Exception("ERROR: Source path must be supplied");
            if (! isset($request['to'])   || strlen($request['to'])   === 0) throw new \Exception("ERROR: Destination path must be supplied");
            if (preg_match("/\.\./",$request['to']))   throw new \Exception("Invalid path name ('..' included)");
            if (preg_match("/\.\./",$request['from'])) throw new \Exception("Invalid path name ('..' included)");
            
            $from = preg_replace("/\/\/+/", "/", $user_document_root . "/" . $request['from']);   # remove duplicate '/'s
            if (! file_exists($from)) throw new \Exception("ERROR: Source file does not exist");

            $to   = preg_replace("/\/\/+/", "/", $user_document_root . "/" . $request['to']);     # remove duplicate '/'s
            if (is_dir($from) && is_dir($to)) $to = $to . '/' . basename($from);
            if (file_exists($to)) throw new \Exception("ERROR: Destination file already exists");

            if ($to) {
                if (preg_match("/\.\./",$to)) throw new \Exception("Invalid directory name (includes '..')");
            }
            
            if (@rename($from,$to) === false) {
                $message = "Unable to move file or directory";
                $error = error_get_last();
                if (isset($error) && isset($error['message'])) {
                    $errmsg = $error['message'];
                    $errmsg  = preg_replace('/ *\\([^\\(\\)]*\\)/', '', $errmsg);
                    if (preg_match('/^.*: *(.*)$/', $errmsg, $matches)) {
                        $errmsg = $matches[1];
                    }
                    $message .= ": $errmsg";
                }
                //if ($test_mode) $message .= " ($dir)";
                throw new \Exception($message);
            }
        }
        else if ($op === 'mkdir') {
            $response['success'] = true;
            $dir_path = (isset($request['dir_path']) && $request['dir_path']) ? $request['dir_path'] : '';
            if (!$dir_path) throw new \Exception("ERROR: New directory path (dir_path) must be supplied");
            if (preg_match("/\.\./",$dir_path)) throw new \Exception("Invalid directory path (includes '..')");
            $new_dir = $user_document_root . "/" . $dir_path;
            $dir_name = (isset($request['dir_name']) && $request['dir_name']) ? $request['dir_name'] : '';
            if ($dir_name) {
                if (preg_match("/\.\./",$dir_name)) throw new \Exception("Invalid directory name (includes '..')");
                $new_dir .= '/'.$dir_name;
            }
            if (@mkdir($new_dir, 02775, true) === false) {
                $message = "Unable to create directory";
                $error = error_get_last();
                if (isset($error) && isset($error['message'])) {
                    $errmsg = $error['message'];
                    $errmsg  = preg_replace('/ *\\([^\\(\\)]*\\)/', '', $errmsg);
                    if (preg_match('/^.*: *(.*)$/', $errmsg, $matches)) {
                        $errmsg = $matches[1];
                    }
                    $message .= ": $errmsg";
                }
                //if ($test_mode) $message .= " ($dir)";
                throw new \Exception($message);
            } #throw new \Exception("Unable to create directory");
        }
        else if ($op === 'delete') {
            $response['success'] = true;
            if (! isset($request['dir_path']) || strlen($request['dir_path'] === 0)) throw new \Exception("ERROR: Directory path must be supplied");
            
            $dir_path = $request['dir_path'];
            $dir = preg_replace("/\/\//", "/", $user_document_root . '/' . $dir_path);

            if (is_dir($dir)) {
                $dir_contents = scandir($dir);
                if (! $this->_is_dir_empty($dir)) {
                    if (isset($request['force']) && $request['force'] == 1) {
                        $path = $dir;
                        while (count(scandir($path)) != 2 || ($path !== $dir)) {
                            foreach (scandir($path) as $item) {
                                if ($item === '.' || $item === '..')
                                    continue;
                                
                                $item = "{$path}/{$item}";
                                
                                if (is_file($item)) {
                                    if (unlink($item) === false)
                                        throw new \Exception("ERROR: could not remove file [{$item}]");
                                }
                                else if (count(scandir($item)) > 2) {
                                    $path = $item;
                                    break;
                                }
                                else if (rmdir($item) === false)
                                    throw new \Exception("ERROR: could not empty directory");
                                
                                // if dir is empty move up one dir
                                if (count(scandir($path)) == 2 && $path != $dir)
                                    $path = preg_replace("/\/[^\/]+[\/]{0,1}$/","",$path);
                            }
                        }
                    }
                    else {
                        $message = "Directory is not empty [$dir_path]";
                        if ($test_mode) $message .= " ($dir)";
                        throw new \Exception($message);
                    }
                }
                if (@rmdir($dir) === false) {
                    $message = "Unable to remove directory [$dir_path]";
                    $error = error_get_last();
                    if (isset($error) && isset($error['message'])) {
                        $errmsg = $error['message'];
                        if (preg_match('/^.*: *(.*)$/', $errmsg, $matches)) {
                            $errmsg = $matches[1];
                        }
                        $message .= ": $errmsg";
                    }
                    //if ($test_mode) $message .= " ($dir)";
                    throw new \Exception($message);
                }
            }
            else if (is_file($dir)) {
                if (unlink($dir) == false) {
                    $message = "Unable to remove file [$dir_path]";
                    if ($test_mode) $message .= " ($dir)";
                    throw new \Exception($message);
                }
            }
            else {
                $message = "File or directory does not exist [$dir_path]";
                if ($test_mode) $message .= " ($dir)";
                throw new \Exception($message);
            }
        }
        else {
            $response['success'] = false;
            $response['message'] = "Cannot perform operation ('$op' is not a valid operation)";
        }
        if ($trace) trace_exit($response);
    }

    private function _is_dir_empty ($dir) {
      if (!is_readable($dir)) return null; 
      $dirh = opendir($dir);
      $empty = true;
      while (false !== ($entry = readdir($dirh))) {
          if ($entry !== '.' && $entry !== '..') {
              $empty = false;
              break;
          }
      }
      closedir($dirh);
      return true;
    }
}
